using System.Data.SqlServerCe;
using System.Data.SqlClient;
using System.Data.SqlTypes;
using System.Data;
using System.IO;
using System.Collections.Generic;
using GoogleEarthClasses.Entities;
using GoogleEarthClasses.Logic;


namespace GoogleEarthClasses.Global
{
    class MySqlCeEngine
    {
        const string K_TempDirectory = ".\\Temp\\";
        private string m_MobileDatabase;
        public string MobileDatabase
        {
            get { return m_MobileDatabase; }
            set { m_MobileDatabase = value; }
        }

        /// <summary>
        /// Connect to the local temporary Database
        /// </summary>
        /// <param name="database"></param>
        public MySqlCeEngine(string Database)
        {
            m_MobileDatabase = Database;
        }
        /// <summary>
        /// Insert all mobile points and images in a local Server
        /// </summary>
        /// <returns></returns>
        public DataTable CopyPoints()
        {
            SqlCeConnection _MobileConnection = null;
            string _Query;
            try
            {
                //instance connection to mobile Databases
                _MobileConnection = new SqlCeConnection("Data Source = " + m_MobileDatabase + " ");
                if (_MobileConnection.State == ConnectionState.Closed)
                    _MobileConnection.Open();
                //made select query;
                //_Query = "select P.IdPosition as LocationId, fLongitude as Longitude, fLatitude as Latitude, fAltitude as Altitude, tDescription as Description, fAngle as Angle, dSatelitetime as SatelliteTime, nSatelites as Satellites, sName as Name,ImageBytes as Bytes";
                //_Query += "       From tb_Position as P";
                //_Query += "      LEFT JOIN tb_Images as I on P.IdPosition = I.IdPosition";
                //_Query += "      ORDER BY P.IdPosition ";
                //_Query = "select P.PositionId as LocationId, Longitude, Latitude, Altitude, Description, Angle, SatelliteTime, SatellitesCount as Satellites, Name,Bytes,CountImages ";
                _Query = "select P.PositionId as LocationId, Longitude, Latitude, Altitude, Description, Angle, SatelliteTime, SatellitesCount as Satellites,CountImages,UploadedPointId, Color ";
                _Query += "       From tb_Positions as P";
                _Query += "      Where CompletedDate is null";
                _Query += "      ORDER BY CountImages ";

                SqlCeCommand _SqlCeCommand;
                _SqlCeCommand = new SqlCeCommand(_Query, _MobileConnection);
                SqlCeDataAdapter _SqlCeDataAdapter;
                _SqlCeDataAdapter = new SqlCeDataAdapter(_Query, _MobileConnection);
                DataTable dt = new DataTable();
                //Fill Datatable with mobile data;
                _SqlCeDataAdapter.Fill(dt);
                return dt;
            }
            catch (System.Exception ex)
            {
                throw ex;
                
            }
            finally
            {
                _MobileConnection.Close();
                _MobileConnection.Dispose();
            }
        }
        public static bool TestConnection(string ConnString)
        {
            SqlCeConnection _MobileConnection = null;
            try
            {
                _MobileConnection = new SqlCeConnection("Data Source = " + ConnString + " ");
                if (_MobileConnection.State == ConnectionState.Closed)
                    _MobileConnection.Open();
                return true;
            }
            catch (System.Exception)
            {
                return false;
            }


        }

        public bool UpdateInsertedPosition(int IdPositionInserted, int IdNewPosition )
        {
            SqlCeConnection _MobileConnection = null;
            string _Query;
            try
            {
                //instance connection to mobile Databases
                _MobileConnection = new SqlCeConnection("Data Source = " + m_MobileDatabase + " ");
                if (_MobileConnection.State == ConnectionState.Closed)
                    _MobileConnection.Open();
                _Query = "Update tb_Positions set UploadedPointId  = " + IdNewPosition.ToString() + " ";
                _Query += " where PositionId = " + IdPositionInserted.ToString()+ " ";
                SqlCeCommand _SqlCeCommand;
                _SqlCeCommand = new SqlCeCommand(_Query, _MobileConnection);
                _SqlCeCommand.ExecuteNonQuery();
                return true;
            }
            catch (System.Exception ex)
            {
                return false;

            }
            finally
            {
                _MobileConnection.Close();
                _MobileConnection.Dispose();
            }
            
        }

        public bool UpdatePositionAmountImages(int IdPositionInserted, int AmountImages)
        {
            SqlCeConnection _MobileConnection = null;
            string _Query;
            try
            {
                //instance connection to mobile Databases
                _MobileConnection = new SqlCeConnection("Data Source = " + m_MobileDatabase + " ");
                if (_MobileConnection.State == ConnectionState.Closed)
                    _MobileConnection.Open();
                _Query = "Update tb_Positions set CountImages  = " + AmountImages.ToString() + " ";
                _Query += " where PositionId = " + IdPositionInserted.ToString() + " ";
                SqlCeCommand _SqlCeCommand;
                _SqlCeCommand = new SqlCeCommand(_Query, _MobileConnection);
                _SqlCeCommand.ExecuteNonQuery();
                return true;
            }
            catch (System.Exception ex)
            {
                return false;

            }
            finally
            {
                _MobileConnection.Close();
                _MobileConnection.Dispose();
            }

        }

        public bool CompleteInsertedPosition(int IdPosition)
        {
            SqlCeConnection _MobileConnection = null;
            string _Query;
            try
            {
                //instance connection to mobile Databases
                _MobileConnection = new SqlCeConnection("Data Source = " + m_MobileDatabase + " ");
                if (_MobileConnection.State == ConnectionState.Closed)
                    _MobileConnection.Open();
                _Query = "Update tb_Positions set CompletedDate  = '" + System.DateTime.Now.ToString("yyyy-MM-dd hh:mm:ss") +"' ";
                _Query += " where PositionId = " + IdPosition.ToString() + " ";
                SqlCeCommand _SqlCeCommand;
                _SqlCeCommand = new SqlCeCommand(_Query, _MobileConnection);
                _SqlCeCommand.ExecuteNonQuery();
                return true;
            }
            catch (System.Exception ex)
            {
                return false;

            }
            finally
            {
                _MobileConnection.Close();
                _MobileConnection.Dispose();
            }

        }

        public bool UpdatePositionReferencesById(int IdPosition, double Latitude,double Longitude, double Altitude, string Color )
        {
            SqlCeConnection _MobileConnection = null;
            string _Query;
            try
            {
                //instance connection to mobile Databases
                _MobileConnection = new SqlCeConnection("Data Source = " + m_MobileDatabase + " ");
                if (_MobileConnection.State == ConnectionState.Closed)
                    _MobileConnection.Open();
                _Query = "Update tb_Positions set ";
                _Query += " Longitude  = '" + Longitude.ToString().Replace(",",".") + "',";
                _Query += " Latitude= '" + Latitude.ToString().Replace(",", ".") + "', ";
                _Query += " Altitude = '" + Altitude.ToString().Replace(",", ".") + "', ";
                _Query += " Color = '"+ Color +"' ";
                _Query += " where PositionId = " + IdPosition.ToString() + " ";
                SqlCeCommand _SqlCeCommand;
                _SqlCeCommand = new SqlCeCommand(_Query, _MobileConnection);
                _SqlCeCommand.ExecuteNonQuery();
                return true;
            }
            catch (System.Exception ex)
            {
                return false;

            }
            finally
            {
                _MobileConnection.Close();
                _MobileConnection.Dispose();
            }
            
        }

        public bool DeletePositionById(int IdPosition)
        {
            SqlCeConnection _MobileConnection = null;
            string _Query;
            try
            {
                //instance connection to mobile Databases
                _MobileConnection = new SqlCeConnection("Data Source = " + m_MobileDatabase + " ");
                if (_MobileConnection.State == ConnectionState.Closed)
                    _MobileConnection.Open();
                _Query = "DELETE FROM tb_Positions ";
                _Query += " where PositionId = " + IdPosition.ToString() + " ";
                SqlCeCommand _SqlCeCommand;
                _SqlCeCommand = new SqlCeCommand(_Query, _MobileConnection);
                _SqlCeCommand.ExecuteNonQuery();
                return true;
            }
            catch (System.Exception ex)
            {
                return false;

            }
            finally
            {
                _MobileConnection.Close();
                _MobileConnection.Dispose();
            }

        }

        public string GetDatabaseVersion()
        {
            SqlCeConnection _MobileConnection = null;
            string _Query;
            try
            {
                //instance connection to mobile Databases
                _MobileConnection = new SqlCeConnection("Data Source = " + m_MobileDatabase + " ");
                if (_MobileConnection.State == ConnectionState.Closed)
                    _MobileConnection.Open();
                _Query = "select Version from tb_Config";
                SqlCeCommand _SqlCeCommand;
                _SqlCeCommand = new SqlCeCommand(_Query, _MobileConnection);
                return (string)_SqlCeCommand.ExecuteScalar();
            }
            catch (System.Exception ex)
            {
                throw;

            }
            finally
            {
                _MobileConnection.Close();
                _MobileConnection.Dispose();
            }
        
        }

        public object GetAmountPositions()
        {
            SqlCeConnection _MobileConnection = null;
            string _Query;
            try
            {
                //instance connection to mobile Databases
                _MobileConnection = new SqlCeConnection("Data Source = " + m_MobileDatabase + " ");
                if (_MobileConnection.State == ConnectionState.Closed)
                    _MobileConnection.Open();
                _Query = "select count(PositionId) from tb_Positions";
                SqlCeCommand _SqlCeCommand;
                _SqlCeCommand = new SqlCeCommand(_Query, _MobileConnection);
                return _SqlCeCommand.ExecuteScalar();
            }
            catch (System.Exception ex)
            {
                throw;

            }
            finally
            {
                _MobileConnection.Close();
                _MobileConnection.Dispose();
            }
        
        }
        public object GetAmountImages()
        {
            SqlCeConnection _MobileConnection = null;
            string _Query;
            try
            {
                //instance connection to mobile Databases
                _MobileConnection = new SqlCeConnection("Data Source = " + m_MobileDatabase + " ");
                if (_MobileConnection.State == ConnectionState.Closed)
                    _MobileConnection.Open();
                _Query = "select sum(CountImages) from tb_Positions";
                SqlCeCommand _SqlCeCommand;
                _SqlCeCommand = new SqlCeCommand(_Query, _MobileConnection);
                return _SqlCeCommand.ExecuteScalar();
            }
            catch (System.Exception ex)
            {
                throw;

            }
            finally
            {
                _MobileConnection.Close();
                _MobileConnection.Dispose();
            }

        }
        public object GetTotalUploaded()
        {
            SqlCeConnection _MobileConnection = null;
            string _Query;
            try
            {
                //instance connection to mobile Databases
                _MobileConnection = new SqlCeConnection("Data Source = " + m_MobileDatabase + " ");
                if (_MobileConnection.State == ConnectionState.Closed)
                    _MobileConnection.Open();
                _Query = "select  count(PositionId) from tb_Positions Where CompletedDate is not null";
                SqlCeCommand _SqlCeCommand;
                _SqlCeCommand = new SqlCeCommand(_Query, _MobileConnection);
                return _SqlCeCommand.ExecuteScalar();
            }
            catch (System.Exception ex)
            {
                throw;

            }
            finally
            {
                _MobileConnection.Close();
                _MobileConnection.Dispose();
            }
        
        }

        public object CheckExistingTable(string _Table)
        {
            SqlCeConnection _MobileConnection = null;
            string _Query;
            try
            {
                //instance connection to mobile Databases
                _MobileConnection = new SqlCeConnection("Data Source = " + m_MobileDatabase + " ");
                if (_MobileConnection.State == ConnectionState.Closed)
                    _MobileConnection.Open();
                _Query = "SELECT 1 FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = '" + _Table + "'";
                SqlCeCommand _SqlCeCommand;
                _SqlCeCommand = new SqlCeCommand(_Query, _MobileConnection);
                return _SqlCeCommand.ExecuteScalar();
                
            }
            catch (System.Exception ex)
            {
                return false;
            }
            finally
            {
                _MobileConnection.Close();
                _MobileConnection.Dispose();
            }

        }
        #region ALTER DATABASE TO ADD TABLES OR ALTER EXISTING ONES
        public bool AlterTableCompletedDate()
        {
            SqlCeConnection _MobileConnection = null;
            string _Query;
            try
            {
                //instance connection to mobile Databases
                _MobileConnection = new SqlCeConnection("Data Source = " + m_MobileDatabase + " ");
                if (_MobileConnection.State == ConnectionState.Closed)
                    _MobileConnection.Open();
                _Query = "ALTER TABLE tb_Positions ADD COLUMN [CompletedDate] datetime";
                SqlCeCommand _SqlCeCommand;
                _SqlCeCommand = new SqlCeCommand(_Query, _MobileConnection);
                _SqlCeCommand.ExecuteNonQuery();
                return true;
            }
            catch (System.Exception ex)
            {
                return false;
            }
            finally
            {
                _MobileConnection.Close();
                _MobileConnection.Dispose();
            }
        }

        public bool AlterTableUploadedPointId()
        {
            SqlCeConnection _MobileConnection = null;
            string _Query;
            try
            {
                //instance connection to mobile Databases
                _MobileConnection = new SqlCeConnection("Data Source = " + m_MobileDatabase + " ");
                if (_MobileConnection.State == ConnectionState.Closed)
                    _MobileConnection.Open();
                _Query = "ALTER TABLE tb_Positions ADD COLUMN [UploadedPointId] int";
                SqlCeCommand _SqlCeCommand;
                _SqlCeCommand = new SqlCeCommand(_Query, _MobileConnection);
                _SqlCeCommand.ExecuteNonQuery();
                return true;
            }
            catch (System.Exception ex)
            {
                return false;
            }
            finally
            {
                _MobileConnection.Close();
                _MobileConnection.Dispose();
            }

        }

        public bool AlterTablePositions_AddColorColumn()
        {
            SqlCeConnection _MobileConnection = null;
            string _Query;
            try
            {
                //instance connection to mobile Databases
                _MobileConnection = new SqlCeConnection("Data Source = " + m_MobileDatabase + " ");
                if (_MobileConnection.State == ConnectionState.Closed)
                    _MobileConnection.Open();
                _Query = "ALTER TABLE tb_Positions ADD COLUMN [Color] nvarchar(50)";
                SqlCeCommand _SqlCeCommand;
                _SqlCeCommand = new SqlCeCommand(_Query, _MobileConnection);
                _SqlCeCommand.ExecuteNonQuery();
                return true;
            }
            catch (System.Exception ex)
            {
                return false;
            }
            finally
            {
                _MobileConnection.Close();
                _MobileConnection.Dispose();
            }
        }

        public bool CreateConfigTable()
        {
            SqlCeConnection _MobileConnection = null;
            string _Query;
            try
            {
                //instance connection to mobile Databases
                _MobileConnection = new SqlCeConnection("Data Source = " + m_MobileDatabase + " ");
                if (_MobileConnection.State == ConnectionState.Closed)
                    _MobileConnection.Open();
                _Query = "CREATE TABLE tb_Config(Version nvarchar(255))";
                SqlCeCommand _SqlCeCommand;
                _SqlCeCommand = new SqlCeCommand(_Query, _MobileConnection);
                _SqlCeCommand.ExecuteNonQuery();
                return true;

            }
            catch (System.Exception ex)
            {
                return false;
            }
            finally
            {
                _MobileConnection.Close();
                _MobileConnection.Dispose();
            }

        }
        #endregion ALTER DATABASE TO ADD TABLES OR ALTER EXISTING ONES
    }

}
